const mysql = require('mysql2/promise')
const fs = require('fs')
const path = require('path')

async function initTables () {
  try {
    console.log('🔍 连接到数据库...')

    // 连接到数据库
    const connection = await mysql.createConnection({
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'GKL9512357',
      database: 'novel_platform'
    })

    console.log('✅ 数据库连接成功')

    // 读取SQL文件
    const sqlFile = path.join(__dirname, 'init-database.sql')
    const sqlContent = fs.readFileSync(sqlFile, 'utf8')

    // 分割SQL语句（按分号分割），并过滤掉数据库创建和USE语句
    const statements = sqlContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => {
        const cleanStmt = stmt.toLowerCase()
        return (
          stmt.length > 0 &&
          !stmt.startsWith('--') &&
          !cleanStmt.includes('create database') &&
          !cleanStmt.includes('use ')
        )
      })

    console.log(`📝 准备执行 ${statements.length} 条SQL语句...`)

    // 逐条执行SQL语句
    for (let i = 0; i < statements.length; i++) {
      const stmt = statements[i]
      if (stmt.trim()) {
        try {
          await connection.query(stmt)
          console.log(`✅ 执行语句 ${i + 1}/${statements.length}`)
        } catch (error) {
          // 忽略一些常见的错误（如表已存在等）
          if (
            error.code === 'ER_TABLE_EXISTS_ERROR' ||
            error.code === 'ER_DUP_KEYNAME' ||
            error.code === 'ER_DUP_FIELDNAME'
          ) {
            console.log(
              `⚠️  语句 ${i + 1}/${statements.length} (已存在): ${
                error.message
              }`
            )
          } else {
            console.error(
              `❌ 语句 ${i + 1}/${statements.length} 执行失败:`,
              error.message
            )
          }
        }
      }
    }

    console.log('🎉 数据库表初始化完成')

    // 检查表是否创建成功
    const [tables] = await connection.query('SHOW TABLES')
    console.log(
      '📊 数据库中的表:',
      tables.map(t => Object.values(t)[0])
    )

    // 关闭连接
    await connection.end()
    console.log('🔌 连接已关闭')
  } catch (error) {
    console.error('❌ 初始化数据库表失败:', error.message)
  }
}

initTables()
